package com.artfii.fluentsql.jdbctemplate;

import com.artfii.fluentsql.core.LambdaQuery;
import com.artfii.fluentsql.core.Page;
import com.artfii.fluentsql.core.exception.FluentSqlException;
import com.artfii.fluentsql.core.jdbc.ConnectionBuilder;
import com.artfii.fluentsql.core.mock.Dept;
import com.artfii.fluentsql.core.mock.User;
import com.artfii.fluentsql.core.tools.BeanMapUtils;
import com.artfii.fluentsql.core.tools.Throw;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Logger;

/**
 * Function: 查询表达式
 *
 * @Autor: leeton
 * @Date : 2/27/18
 */
public class Qe<T> extends LambdaQuery<T> {
    protected static final Logger logger = Logger.getLogger("Qe");

    private NamedParameterJdbcTemplate jdbcTemplate;

    public Qe() {
    }

    public Qe(Class<T> clz) {
        init(clz, getTableName(clz), null);
    }

    public Qe(String mainTableName) {
        init(null, mainTableName, null);
    }


    public Qe(Class<T> clz, NamedParameterJdbcTemplate jdbcTemplate) {
        init(clz, getTableName(clz), jdbcTemplate);
    }

    public Qe(NamedParameterJdbcTemplate jdbcTemplate) {
        init(null, null, jdbcTemplate);
    }

    protected Qe<T> init(Class<T> clz, String mainTableName, NamedParameterJdbcTemplate jdbcTemplate) {
        this.id = hashCode();
        this.clz = clz;
        this.mainTableName = mainTableName;
        this.jdbcTemplate = jdbcTemplate;
        this.db_type_name = getDbTypeName();
        return this;
    }
    @Override
    public void engine() {
        this.engine = ConnectionBuilder.SqlEngine.spring_named_jdbc;
    }

    @Override
    public Qe<T> condition() {
        Qe q = new Qe(this.clz);
        q.clear();
        q.onlyConition = true;
        return q;
    }

    private String buildCount() {
        String sql = settingParams(buildCountSymbolSql());
        return sql;
    }

    // ====== 集成查询方法 BEGIN ====================================================================================================
    public T to() {
        return to(this.clz);
    }

    public T to(Class tClass) {
        String finalSql = build();
        printSql(finalSql);
        List<T> list = getList(finalSql, tClass);
        return 0 == list.size() ? null : list.get(0);
    }

    public Long toCount() {
        String sql = this.buildCount();
        printSql(sql);
        return single(sql, Long.class);
    }

    public List<T> toList() {
        return toList(this.clz);
    }

    public List toList(Class tClass) {
        String finalSql = build();
        printSql(finalSql);
        return getList(finalSql, tClass);
    }

    public int toDel() {
        checkProvider(jdbcTemplate);
        String finalSql = delete().build();
        printSql(finalSql);
        int rows = jdbcTemplate.update(finalSql,new HashMap());
        return rows;
    }

    public int toUpdate(Object entity) {
        checkProvider(jdbcTemplate);
        String finalSql = update(entity).build();
        printSql(finalSql);
        int rows = jdbcTemplate.update(finalSql, new HashMap<>());
        clear();
        return rows;
    }

    public int toBatchInsert(List<?> batchValues) {
        if (null == batchValues || batchValues.size() == 0) return 0;
        checkProvider(jdbcTemplate);
        String insertSql = buildBatchInsertSql().buildSymbolsql();
        Throw.isTrue(insertSql.toLowerCase().indexOf("where") != -1, "Batch INSERT canot include [WHERR] condition. " + insertSql);
        int[] nums = jdbcTemplate.batchUpdate(insertSql, listToMapArr(batchValues));
        printSql(insertSql);
        clear();
        return nums.length;
    }

    private Map<String, ?>[] listToMapArr(List<?> batchValues) {
        Map<String, ?>[] mapArr = new HashMap[batchValues.size()];
        int i = 0;
        for (Object o : batchValues) {
            Map<String, Object> oMap = new HashMap<>(o.getClass().getDeclaredFields().length);
            BeanMapUtils.builder().toUnderline().c(o, oMap);
            mapArr[i] = oMap;
            i++;
        }
        return mapArr;
    }

    public int toSave(Object entity) {
        checkProvider(jdbcTemplate);
        String finalSql = save(entity).build();
        printSql(finalSql);
        Map<String, Object> jdbdParams = toJdbcParams(params);
        int rows = jdbcTemplate.update(finalSql, new HashMap<>());
        clear();
        return rows;
    }

    public Page<T> toPage(Page page) {
        return toPage(this.clz, page);
    }

    public Page<T> toPage(Class clz, Page page) {
        page = getPage(clz, page);
        clear();
        return page;
    }

    /**
     * 返回 PAGE 查询结果集
     * 注意不要传入 limit 语句
     *
     * @param clazz
     * @param page
     * @return
     */
    private Page<T> getPage(Class<T> clazz, Page page) {
        checkProvider(jdbcTemplate);
        StringBuffer limit = templateMap.get("limit");
        if (limit.length() > 0) {
            throw new FluentSqlException("分页参数不要通过[limit]传入.");
        }
        Long count = page.getTotal() == 0 ? toCount() : page.getTotal();
        List<T> list = Collections.emptyList();
        if (count > 0) {
            long pageNumber = page.getPageNumber();
            long pageSize = page.getPageSize();
            long offset = (pageNumber - 1) * pageSize ;
            settingLimit(offset, pageSize);
            String finalSql = build();
            printSql(finalSql);
            list = getList(finalSql, clazz);
        }
        page.setTotal(count);
        page.setItems(list);
        return page;
    }

    /**
     * 返回列表
     * @param finalSql 已经完全拼装并设值的SQL
     * @param tClass
     * @return
     */
    public List getList(String finalSql,Class<T> tClass) {
        checkProvider(jdbcTemplate);
        List<T> list = jdbcTemplate.query(finalSql, new BeanPropertyRowMapper<>(tClass));
        return 0 == list.size() ? new ArrayList<>() : list;
    }

    /**
     * 返回单列的值，通常用于统计
     * @param sql
     * @param params
     * @param tClass
     * @param <T>
     * @return
     */
    public <T> T single(String sql, Map<String, Object> params, Class tClass) {
        checkProvider(jdbcTemplate);
        List<T> list = jdbcTemplate.query(sql, params, new SingleColumnRowMapper<>(tClass));
        return list.size() == 0 ? null : list.get(0);
    }

    public <T> T single(String finalSql, Class tClass) {
        checkProvider(jdbcTemplate);
        List<T> list = jdbcTemplate.query(finalSql, new SingleColumnRowMapper<>(tClass));
        return list.size() == 0 ? null : list.get(0);
    }

    public <T> T getObj(String sql, Map<String, Object> params, Class tClass) {
        String finalSql = build();
        printSql(finalSql);
        List<T> list = getList(finalSql,tClass);
        return list.size() == 0 ? null : list.get(0);
    }

    private void checkProvider(NamedParameterJdbcTemplate jdbcTemplate) {
        Throw.isNull(jdbcTemplate, "NamedParameterJdbcTemplate 不能为 NULL,请先传入.");
    }

    private String getDbTypeName() {
        try {
            if (null != jdbcTemplate) {
                DataSource dataSource = ((JdbcTemplate) jdbcTemplate.getJdbcOperations()).getDataSource();
                this.db_type_name = dataSource.getConnection().getMetaData().getDatabaseProductName();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return db_type_name;
    }


    // ====== 集成查询方法 END ====================================================================================================


    public static void main(String[] args) throws Exception {
        String sql = new Qe(User.class)
                .select("user_uame")
//            .andIn("dept_id", new Qe(Dept.class).select("id").andGt("id", 0))
                .sum("id", Dept.class)
//                .sumCase("id", 1, "money", "money")
                .leftJoin(Dept.class)
                .andLike("user_uame", "alice")
                .andIn("id", new Integer[]{1, 2, 3})
//                .andBetween("create_date", new Date(), new Date())
                .or(new Qe(User.class).andIn("id", new Integer[]{1, 2, 3}),new Qe(User.class).andEq("user_uame", "alice"))
                .group("dept_id")
                .having("dept_id", Opt.GT, 0)
                .asc("dept_id")
                .desc("user_uame")
                .build();

        System.out.println("sql=" + sql);

     /*   String and_sql = new Qe().eq(SysUser.Dao.userName, "linton").and(new Qe<SysUser>().eq(SysUser.Dao.userName, "alice")).sql();
        System.out.println("and_sql=" + and_sql);


        String or = new Qe().eq(SysUser.Dao.deptId, 1).or(new Qe().eq(SysUser.Dao.deptId, 2)).sql();
        System.out.println("or=" + or);
*/

/*
        String between = new Qe().andBetween(SysUser.Dao.createDate, new Date(), new Date()).build();
        System.out.println("between=" + between);
*/

/*        String del = new Qe(SysUser.class).del().andEq("dept_id", 1).build();
        System.out.println("del= " + del);

        String count = new Qe(SysUser.class).andEq("dept_id", 1).count();
        System.out.println("count= " + count);*/
;
    }


}
